﻿import traceback
from datetime import date
from string import Template
import math
from decimal import *

from DataAccess.DBConnFactory import DBConnFactory
from DataAccess.blp_client import query_blp_data
from Routine.UpdateDivd import *
from Error.ExtendError import *

import timeit


#daily routine!
def update_position_driven_by_repo_expire(ref_date):
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()

	###TODO: what if no daily cash postion
	sql_tpl = Template('''update 
						(select /*+BYPASS_UJVC*/ pos.amount amount,tmp.cf cf
						from position pos
						join (select sum(trd.amount*(1+trd.cost_price*ri.tfraction)) cf, 
								trd.portfolio_id,
								trd.currency, 
								ri.maturity_date cf_date
							from repo_info_ex ri 
							join transaction trd 
								on trd.ticker=ri.ticker 
								and ri.start_date=trd.trade_date 						
							where ri.maturity_date=TO_DATE('${DATE}','yyyy-mm-dd') 
								and trd.security_type='REPO'
							group by trd.portfolio_id,
								trd.currency,
								ri.maturity_date
							) tmp	
						on pos.portfolio_id=tmp.portfolio_id 
							and pos.price_currency=tmp.currency
							and pos.ref_date=tmp.cf_date
							and pos.security_type='CASH'
							and pos.ticker='9999999')
						set amount=amount+cf ''')
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))

	#sql_tpl = Template('''insert into position 
	#				(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
	#				select distinct '${DATE}',trd.portfolio_id,CONCAT(trd.ticker,'.',date_format(trd.trade_date,'%Y%m%d')),trd.security_type,0,0.0,trd.currency 
	#				from repo_info ri 
	#				join transaction trd on trd.ticker=ri.ticker and ri.start_date=trd.trade_date
	#				where ri.maturity_date='${DATE}' and trd.security_type='REPO' ''')
	sql_tpl = Template('''insert into position 
					(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
					SELECT DISTINCT TO_DATE('${DATE}','yyyy-mm-dd'), 
						pos.portfolio_id, 
						pos.ticker, 
						pos.security_type, 
						0, 
						0.0, 
						pos.price_currency
					FROM repo_info ri
					JOIN position pos 
						ON pos.ticker=ri.ticker || '.' || TO_CHAR(ri.start_date,'yyyymmdd')
					WHERE ri.maturity_date=TO_DATE('${DATE}','yyyy-mm-dd') 
						AND pos.security_type='REPO' ''')
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))		
	pke_conn.commit()
	
	
def update_position_driven_by_transaction(ref_date):	
	#DEBUG, Performance tuning
	#start = timeit.default_timer()
	
	update_equity_position_driven_by_transaction(ref_date) 		#including stock and pnote
	update_cash_position_driven_by_equity_transaction(ref_date) #including stock and pnote
	update_cash_position_driven_by_futures_settlement(ref_date)
	update_repo_and_cash_position_driven_by_repo_transaction(ref_date)
	update_cash_position_driven_by_other_adjust(ref_date)
	
	#elapsed = (timeit.default_timer() - start)
	#print 'update_position_driven_by_transaction, ', elapsed
	
	
def update_position_driven_by_dividend(ref_date):	
	#update_daily_stock_dividend_from_cache(ref_date) #SHORT_CUT
	update_daily_stock_dividend_from_vendor(ref_date)
	
	update_equity_position_driven_by_dividend(ref_date)  #include stock and pnote
	update_cash_position_driven_by_dividend(ref_date)
	
 		
def update_daily_position(ref_date):
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	
	pke_cursor.execute('''truncate table tmp_daily_position''')
	sql_tpl = Template('''insert into tmp_daily_position
						select * from 
						(select tmp.ref_date,
							   p.portfolio_id,
							   p.ticker,
							   p.security_type,
							   p.amount,
							   p.avg_cost_price,
							   p.price_currency,
							   p.market
						from position p
						join (
							select cld.ref_date,max(p2.ref_date) av_date,
								p2.portfolio_id,p2.security_type,p2.ticker,p2.price_currency
							from comm_calendar cld
							join (select ref_date,portfolio_id,security_type,ticker,price_currency from position) p2
								on p2.ref_date<=cld.ref_date
							group by cld.ref_date,p2.portfolio_id,p2.security_type,p2.ticker,p2.price_currency
						) tmp
						on p.ref_date=tmp.av_date
							and p.portfolio_id=tmp.portfolio_id
							and p.security_type=tmp.security_type
							and p.ticker=tmp.ticker
							and p.price_currency=tmp.price_currency
						where p.security_type='CASH' or p.amount <> 0 )
						where ref_date=to_date('${DATE}','yyyy-mm-dd')''')
	
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	pke_conn.commit()
	
	
	
def update_repo_and_cash_position_driven_by_repo_transaction(ref_date):
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	
	###update repo position
	sql_tpl = Template('''insert into position 
		(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
		select trd.trade_date, 
			trd.portfolio_id, 
			trd.ticker || '.' || TO_CHAR(trd.trade_date,'yyyymmdd'), 
			trd.security_type, 
			trd.amount, 
			trd.cost_price, 
			trd.currency 
		from transaction trd 
		where trd.trade_date=TO_DATE('${DATE}','yyyy-mm-dd')
			and trd.security_type='REPO' ''')
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	
	###default buy repo, subtract the cash
	###update cash position effected by repo
	###if no repo trade, no position entry modified.
	sql_tpl = Template('''update 
		(select /*+BYPASS_UJVC*/ trd.repo_sum,pos.amount
		from 
			(select trade_date,portfolio_id,currency,
			sum(amount+amount*NVL(commission_fee,0)) repo_sum
			from transaction 
			left join repo_fee 
			on repo_fee.ticker=transaction.ticker
			where trade_date=TO_DATE('${DATE}','yyyy-mm-dd') 
				and security_type='REPO'
			group by trade_date,portfolio_id,currency) trd
		join position pos 
			on pos.ref_date=trd.trade_date 
			and pos.portfolio_id=trd.portfolio_id 
			and pos.price_currency=trd.currency
			and pos.security_type='CASH' 
			and pos.ticker='9999999'
		) 
		set amount=amount-repo_sum
		''')   
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))	
	pke_conn.commit()
	

def update_cash_position_driven_by_other_adjust(ref_date):
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	 
	sql_tpl = Template('''update(
					select /*+BYPASS_UJVC*/ pos.amount amount,adj.cf
					from position pos
					join (
					select ref_date,portfolio_id,currency,sum(amount) cf
					from cash_adj
					where ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
					group by ref_date,portfolio_id,currency
					) adj
					on pos.ref_date=adj.ref_date 
						and pos.portfolio_id=adj.portfolio_id 
						and pos.price_currency=adj.currency
						and pos.security_type='CASH' 
						and pos.ticker='9999999'
					)
					set amount=amount+cf''')   
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	pke_conn.commit()
	
	

###STOCK & PNOTE security position update
def update_equity_position_driven_by_transaction(ref_date):  
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	
	sql_tpl = Template('''select trd.ticker, 
			trd.trade_type, 
			trd.portfolio_id, 
			trd.security_type, 
			trd.amount, 
			trd.cost_price, 
			NVL(dp.amount,0), 
			NVL(dp.avg_cost_price,0.0), 
			trd.currency,
			trd.market 
		from transaction trd 
		left join daily_position dp 
			on trd.ticker=dp.ticker 
			and trd.portfolio_id=dp.portfolio_id 
			and trd.security_type=dp.security_type 
			and trd.trade_date=dp.ref_date 
		where trd.trade_date=TO_DATE('${DATE}','yyyy-mm-dd') 
			and trd.amount<>0  
			and (trd.security_type='STOCK' 
				or trd.security_type='PNOTE' 
				or trd.security_type='FUTURES'
				or trd.security_type='WARRANT'
				or trd.security_type='FUND'
				or trd.security_type='BOND') ''')
	
	sql_text = sql_tpl.substitute(DATE=ref_date.isoformat())	
	pke_cursor.execute(sql_text)
	r = pke_cursor.fetchall()
	#print len(r)
	#print r
	
	for entry in r:
		#print entry
		
		ticker = entry[0]
		trd_type = entry[1].upper()
		portf_id = entry[2]
		sec_t = entry[3]		
		trd_amt = entry[4] 
		if(trd_type=='BUY' or trd_type=='SHORT COVERING' or trd_type=='SC'):
			trd_amt = entry[4]
		else: #trd_type=='SELL' or trd_type='SHORT SELLING' or trd_type='SS'
			trd_amt = -entry[4]
		
		trd_cp = entry[5]
		last_hld = entry[6]		
		last_avg_cp = entry[7]	
		curncy = entry[8]		
		mkt = entry[9]
		if not mkt:
			mkt = ''
		
		#if trd_type=='SELL' and ((not last_hld) or last_hld<0):
		if trd_type=='SELL' and last_hld<0:
			print portf_id,ticker,trd_amt,last_hld
			raise SellingOnShortPositionError(), ("Sell on Short Postion Error.")
		#if (trd_type=='SHORT COVERING' or trd_type=='SC') and ((not last_hld) or last_hld>0): 
		if (trd_type=='SHORT COVERING' or trd_type=='SC') and (last_hld>0): 
			raise CoveringOnLongPositionError(), ("Covering on Long Position Error.")
		 		
		if not last_hld:
			today_hld = trd_amt
			today_avg_cp = trd_cp
		else:	
			today_hld = last_hld + trd_amt
			
			if last_hld*trd_amt >= 0:
				today_avg_cp = (last_hld*last_avg_cp + trd_amt*trd_cp)/today_hld
				#print 1, last_hld*trd_amt, today_avg_cp
			elif abs(last_hld) < abs(trd_amt):
				today_avg_cp = trd_cp
				#print 2, today_avg_cp
			else:
				today_avg_cp = last_avg_cp	
				#print 3, today_avg_cp
							
		sql_tpl = Template('''merge into position p
							using (select '${DATE}' d,'${PORTF_ID}' pid,'${TICKER}' tk,'${SEC_T}' sc,${HLD} h,${AVG_CP} avg,'${CURNCY}' c,'${MKT}' m from dual) t
								on (p.ref_date=TO_DATE(t.d,'yyyy-mm-dd') and p.portfolio_id=t.pid and p.ticker=t.tk and p.security_type=t.sc and p.price_currency=t.c)
							when matched then update set
								p.amount=t.h,p.avg_cost_price=t.avg
							when not matched then insert values
								(TO_DATE(t.d,'yyyy-mm-dd'),t.pid,t.tk,t.sc,t.h,t.avg,t.c,t.m)''')
		sql_text = sql_tpl.substitute(DATE=ref_date.isoformat(), 
									PORTF_ID=portf_id, 
									TICKER=ticker, 
									SEC_T=sec_t, 
									HLD=today_hld,
									AVG_CP=today_avg_cp, 
									CURNCY=curncy,
									MKT=mkt)
		#print sql_text						
		pke_cursor.execute(sql_text)
	
	pke_conn.commit()
	

def update_cash_position_driven_by_equity_transaction(ref_date):
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()

	#sql_tpl = Template('''insert into position 
	#			(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
  # 			 select '${DATE}',portfolio_id,'9999999','CASH', sum(if(trade_type='BUY',-amount*cost_price,amount*cost_price)),1,currency
  #              from transaction where trade_date='${DATE}' group by portfolio_id, currency 
  #              on duplicate key update amount=amount+
  #              (select sum(if(trade_type='BUY',-amount*cost_price,amount*cost_price)) from transaction 
  #               where trade_date='${DATE}' group by portfolio_id, currency) ''')
	
	sql_tpl = Template('''update 
					(select /*+BYPASS_UJVC*/ pos.amount,trd.cf
					from (select trade_date,
						portfolio_id,
						sum(decode(trade_type,'BUY',-amount,'SHORT COVERING',-amount,'SC',-amount,amount)*cost_price) as cf,
						currency
						from transaction 
						where trade_date=TO_DATE('${DATE}','yyyy-mm-dd')
							and (security_type='STOCK' 
								or security_type='PNOTE' 
								or security_type='WARRANT'
								or security_type='FUND'
								or security_type='BOND') 
						group by trade_date,portfolio_id,currency
					 ) trd 
					 join position pos 
					 on pos.ref_date=trd.trade_date 
						and pos.portfolio_id=trd.portfolio_id 
						and pos.price_currency=trd.currency 
						and pos.security_type='CASH' 
						and pos.ticker='9999999') 
		             set amount=amount+cf''')
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	pke_conn.commit()
	
	
def update_cash_position_driven_by_futures_settlement(ref_date):
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()

	sql_tpl = Template('''select trd.trade_type, 
						trd.portfolio_id, 
						trd.amount, 
						trd.cost_price, 
						dp.amount, 
						dp.avg_cost_price, 
						trd.currency, 
						trd.ticker 
					from transaction trd 
					left join daily_position dp 
						on trd.ticker=dp.ticker 
						and trd.portfolio_id=dp.portfolio_id 
						and trd.security_type=dp.security_type 
						and dp.ref_date=trd.trade_date-1
					where trd.trade_date=TO_DATE('${DATE}','yyyy-mm-dd')
						and trd.security_type='FUTURES' 
						and (trd.trade_type='SELL' 
							or trd.trade_type='SHORT COVERING' 
							or trd.trade_type='SC') ''')

	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	r = pke_cursor.fetchall()
	
	#key_type: (portfolio_id, currency)
	tmp = {}
	for entry in r:	
		trade_type = entry[0]
		portf_id = entry[1]
		trd_amt = entry[2]
		trd_cp = entry[3]
		pos_amt = entry[4]
		pos_cp = entry[5]
		curncy = entry[6]
		ticker = entry[7]
		
		sql_tpl = Template('''select "value" from comm_misc_static_info 
						where ticker='${TK}' and field='FUT_VAL_PT' ''')
		pke_cursor.execute(sql_tpl.substitute(TK=ticker))
		fut_val_per_pt = pke_cursor.fetchone()[0]
		#print fut_val_per_pt
		
		if trade_type=='SELL':		
			amt = min(trd_amt, pos_amt)  #require pos_amt>0
			cf = amt * (trd_cp-pos_cp) * fut_val_per_pt
			tmp.setdefault((portf_id,curncy), 0.0)
			tmp[(portf_id,curncy)] += cf
			
		else: #trade_type='SHORT COVERING' or 'SC'			
			amt = min(trd_amt, -pos_amt)  #require pos_amt<0
			cf = amt * (pos_cp-trd_cp) * fut_val_per_pt
			tmp.setdefault((portf_id,curncy), 0.0)
			tmp[(portf_id,curncy)] += cf
	
	sql_tpl = Template('''update position pos 
		             set pos.amount=pos.amount+${CF} 
		             where pos.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
						and pos.portfolio_id='${PORTF_ID}'
						and pos.security_type='CASH' 
						and pos.price_currency='${CURNCY}' ''')
	for (k,v) in tmp.items():
		sql_text = sql_tpl.substitute(DATE=ref_date.isoformat(),CF=v, PORTF_ID=k[0],CURNCY=k[1])
		pke_cursor.execute(sql_text)
	pke_conn.commit()	
			
	
		
def update_cash_position_driven_by_dividend(ref_date):
	#DEBUG, Performance tuning
	#start = timeit.default_timer()
	
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	
	sql_tpl = Template('''insert into position 
					(ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency)
					select TO_DATE('${DATE}','yyyy-mm-dd'),
						divd.portfolio_id,
						'9999999',
						'CASH',
						NVL(pos.amount,0)+divd.cf,
						1,
						divd.currency
					from (select portfolio_id, sum(amount) as cf,currency 
							from cash_dividend_flow 
							where event_date=TO_DATE('${DATE}','yyyy-mm-dd') 
							group by portfolio_id, currency) divd 
					left join (select * from daily_position pos 
								where pos.security_type='CASH' 
								and ref_date=TO_DATE('${DATE}','yyyy-mm-dd')) pos
						on pos.portfolio_id=divd.portfolio_id 
						and pos.price_currency=divd.currency 
					where divd.cf<>0 ''')
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	pke_conn.commit()

	#sql_tpl = Template('''update position pos,
	#							(select event_date,portfolio_id,sum(amount) as cf,currency
	#	             from cash_dividend_flow where event_date='${DATE}' group by portfolio_id,currency) divd 
	#	             set pos.amount=pos.amount+divd.cf 
	#	             where pos.ref_date=divd.event_date and pos.portfolio_id=divd.portfolio_id and pos.security_type='CASH' 
	#	             and pos.price_currency=divd.currency ''') 	
	
	#first insert blank entry, then update the amount column
	sql_tpl = Template('''merge into position p
					using (select TO_DATE('${DATE}','yyyy-mm-dd') d,pos.portfolio_id pid,'9999999' tk,'CASH' sc,pos.amount h,pos.avg_cost_price avg,pos.price_currency c
							from daily_position pos 
							where pos.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
								and pos.security_type='CASH') t
					on (p.ref_date=t.d and p.portfolio_id=t.pid and p.ticker=t.tk and p.security_type=t.sc and p.price_currency=t.c)
					when matched then update set
						p.avg_cost_price=1
					when not matched then insert (ref_date,portfolio_id,ticker,security_type,amount,avg_cost_price,price_currency) values
						(t.d,t.pid,t.tk,t.sc,t.h,t.avg,t.c)''')					
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	pke_conn.commit()
	
	#elapsed = (timeit.default_timer() - start)
	#print 'update_cash_position_driven_by_dividend, ', elapsed


def update_equity_position_driven_by_dividend(ref_date):
	#DEBUG, Performance tuning
	#start = timeit.default_timer()
		
	pke_conn = DBConnFactory().get_db_connection('PKEDB')
	pke_cursor = pke_conn.cursor()
	
	#
	##########   update cash dividend flow, STOCK
	sql_tpl = Template('''insert into cash_dividend_flow
					(event_date,ticker,portfolio_id,security_type,amount,currency)	
					select dp.ref_date, 
						dp.ticker, 
						dp.portfolio_id, 
						'STOCK',
						ROUND(dp.amount*
							decode(sign(dp.amount),1,
								decode(conf.long_pos_tax,'EX_TAX',t.cash_divd_extax,'EX_TAX_QFII',t.cash_divd_extax_qfii,t.cash_divd),
								decode(conf.short_pos_tax,'EX_TAX',t.cash_divd_extax,'EX_TAX_QFII',t.cash_divd_extax_qfii,t.cash_divd))
							,2) as amount, 
						t.currency 
					from daily_position_tick_ex dp
					join tmp_dividend t 
						on dp.lticker=t.ticker
						and dp.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')
						and dp.amount<>0 
						and t.cash_divd>0 
						and dp.security_type='STOCK' 
					join divd_tax_conf conf
						on conf.portfolio_id=dp.portfolio_id''')
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	
	#
	##########   update cash dividend flow, PNOTE
	sql_tpl = Template('''insert into tmp_pnote_position 
					select dp.portfolio_id,dp.ticker,pn.underlying_ticker,
						dp.amount,dp.avg_cost_price,
						pn.currency,pn.underlying_currency 
					from daily_position dp
					join pnote_info pn 
						on dp.ticker=pn.isin_code 
						and ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
						and security_type='PNOTE' ''')
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
							
	sql_tpl = Template('''insert into cash_dividend_flow 
					(event_date,ticker,portfolio_id,security_type,amount,currency)	
					select TO_DATE('${DATE}','yyyy-mm-dd'),
						tpp.isin, 
						tpp.portfolio_id, 
						'PNOTE',
						ROUND(tpp.amount*
							decode(sign(tpp.amount),1,
								decode(conf.long_pos_tax,'EX_TAX_QFII',t.cash_divd_extax_qfii,t.cash_divd),
								decode(conf.short_pos_tax,'EX_TAX_QFII',t.cash_divd_extax_qfii,t.cash_divd))
							/fx.price,2) as amount, 
						tpp.currency 
					from tmp_pnote_position tpp
					join tmp_dividend t 
						on tpp.underlying=t.ticker
					join divd_tax_conf conf
						on conf.portfolio_id=tpp.portfolio_id
					left join comm_fx_latest fx 
						on fx.dom_curncy=tpp.uly_cur 
						and fx.for_curncy=tpp.currency
						and fx.ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 			
					where tpp.amount<>0 
						and t.cash_divd>0 ''') 
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	
	#
	##########   update stock dividend flow
	sql_tpl = Template('''insert into stock_dividend_flow 
					select dp.ref_date, 
						dp.ticker, 
						dp.portfolio_id, 
						round(dp.amount*t.share_divd,0) as amount 
					from daily_position_tick_ex dp
					join tmp_dividend t 
						on dp.lticker=t.ticker
					    and dp.ref_date=TO_DATE('${DATE}','yyyy-mm-dd') 
						and dp.amount<>0 
						and t.share_divd>0
						and (dp.security_type='STOCK' 
							or dp.security_type='PNOTE') ''') 				
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	
	#
	##########   update position table affected by dividend
	
	###BUG:NO FX ADJUST for PNOTE DIVD
	# sql_tpl = Template('''insert into position 					
					# select dp.ref_date, 
						# dp.portfolio_id, 
						# dp.ticker, 
						# dp.security_type, 
						# dp.amount+round(dp.amount*t.share_divd,0) as amount, 
						# (dp.amount*dp.avg_cost_price
						 # -dp.amount*decode(sign(dp.amount),1,t.cash_divd_extax,t.cash_divd))
						# /(dp.amount+round(dp.amount*t.share_divd,0)) as avg_cost_price, 
						# dp.price_currency, 
						# dp.market
					# from daily_position_tick_ex dp
					# join tmp_dividend t 
						# on dp.lticker=t.ticker
					    # and dp.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')  
						# and dp.amount<>0 
						# and (t.share_divd>0 or t.cash_divd>0)
						# and (dp.security_type='STOCK' 
							# or dp.security_type='PNOTE')''') 
							
	sql_tpl = Template('''insert into position 					
					select dp.ref_date, 
						dp.portfolio_id, 
						dp.ticker, 
						dp.security_type, 
						dp.amount+divd.stamt as amount, 
						(dp.amount*dp.avg_cost_price-divd.cshamt)/(dp.amount+divd.stamt) as avg_cost_price, 
						dp.price_currency, 
						dp.market
					from daily_position_tick_ex dp
					join 
						(select nvl(sdivd.ticker,cdivd.ticker) ticker,
								nvl(sdivd.portfolio_id,cdivd.portfolio_id) portfolio_id,
								nvl(cdivd.amount,0) cshamt,
								nvl(sdivd.amount,0) stamt
						from stock_dividend_flow sdivd
						full join cash_dividend_flow cdivd
						on sdivd.ticker=cdivd.ticker 
							and sdivd.portfolio_id=cdivd.portfolio_id
							and sdivd.event_date=cdivd.event_date
						where nvl(sdivd.event_date,cdivd.event_date)=TO_DATE('${DATE}','yyyy-mm-dd')  
						) divd
					on dp.ticker=divd.ticker
					  and dp.portfolio_id=divd.portfolio_id
					  and dp.ref_date=TO_DATE('${DATE}','yyyy-mm-dd')  
					''') 						
	pke_cursor.execute(sql_tpl.substitute(DATE=ref_date.isoformat()))
	pke_conn.commit()
	
	#elapsed = (timeit.default_timer() - start)
	#print 'update_stock_position_driven_by_dividend, ', elapsed

		
